#include "sqlquery.h"
#include "ui_sqlquery.h"
#include <ActiveQt/QAxObject>
#include <QDebug>
#include <QFile>
#include <qjson/parser.h>
#include <QSqlError>
#include <QRegExp>


SqlQuery::SqlQuery(QWidget *parent) :
    QDialog(parent),
    ui(new Ui::SqlQuery)
{
    ui->setupUi(this);

    connect(ui->execButton, SIGNAL(clicked()),
            this, SLOT(execButtonPressed()));
    connect(ui->exitButton, SIGNAL(clicked()),
            this, SLOT(reject()));
    connect(ui->excelButton, SIGNAL(clicked()),
            this, SLOT(exportToExcel()));
}

SqlQuery::~SqlQuery()
{
    delete ui;
}

void SqlQuery::loadQuery(const QString &filename)
{
    qDebug() << "Loading query" << filename;
    QFile file(filename);
    if (!file.open(QIODevice::ReadOnly | QIODevice::Text)) {
        ui->statusLabel->setText(tr("Can't open query declaration, file didn't exist!"));
        return;
    }

    QTextStream in(&file);
    in.setCodec("UTF-8");
    QString json = in.readAll();
    file.close();

    bool ok;
    QJson::Parser parser;
    m_query = parser.parse(json.toUtf8(), &ok).toMap();

    if (!ok) {
        ui->statusLabel->setText(tr("Can't parse query declaration, check syntax!"));
        return;
    }

    QVariantList columns = m_query["columns"].toList();
    m_query.insert("columns_count", columns.count());
    initColumns(columns);
    initArgs(m_query["args"].toList());
    setWindowTitle(tr("Query :: %1").arg(m_query["name"].toString()));
    ui->descLabel->setText(m_query["description"].toString());
    m_query.insert("row_count", 0);

    ui->treeWidget->clear();
}

void SqlQuery::execButtonPressed()
{
    QString sqlQuery(m_query["sql"].toString());
    for (int i = 0; i < m_args.count(); ++i) {
        QString arg = m_args[i]->text();
        if (arg.contains(QRegExp(QString::fromLocal8Bit("[^0-9a-zA-Zа-яА-Я]")))) {
            ui->statusLabel->setText(tr("Invalud argument #%1").arg(i));
            return;
        }

        sqlQuery = sqlQuery.arg(m_args[i]->text());
    }

    execQuery(sqlQuery, m_query["columns_count"].toInt());
}

void SqlQuery::exportToExcel()
{
    if (m_query.value("row_count", 0) == 0) {
        ui->statusLabel->setText(tr("Nothing to export"));
        return;
    }

    QAxObject *excel = new QAxObject("Excel.Application", 0);
    excel->setProperty("SheetsInNewWorkbook", 1);
    QAxObject *workbooks = excel->querySubObject("Workbooks");
    QAxObject *workbook = workbooks->querySubObject("Add");
    //QAxObject *sheets = workbook->querySubObject("Worksheets");
    QAxObject *sheet = workbook->querySubObject("Worksheets(int)", 1);

    QVariantList cols = m_query["columns"].toList();
    for (int i = 0; i < cols.count(); ++i) {
        QAxObject *range = sheet->querySubObject("Cells(int, int)", 1, i + 1);
        range->dynamicCall("SetValue(QVariant)", cols[i]);
    }
    for (int i = 0; i < ui->treeWidget->topLevelItemCount(); ++i) {
        QTreeWidgetItem *item = ui->treeWidget->topLevelItem(i);
        for (int col = 0; col < item->columnCount(); ++col) {
            QAxObject *range = sheet->querySubObject("Cells(int, int)", i + 2, col + 1);
            range->dynamicCall("SetValue(QVariant)", item->text(col));
        }
    }

    excel->dynamicCall("SetVisible(bool)", true);
}

void SqlQuery::initColumns(const QVariantList &names)
{
    QStringList namesList;
    for (int i = 0; i < names.count(); ++i) {
        namesList << names[i].toString();
    }
    ui->treeWidget->setColumnCount(names.count());
    ui->treeWidget->setHeaderLabels(namesList);
}

void SqlQuery::initArgs(const QVariantList &args)
{
    for (int i = 0; i < args.count(); ++i) {
        QLineEdit *lineEdit = new QLineEdit();
        m_args.push_back(lineEdit);

        ui->argsLayout->addRow(args[i].toString(), lineEdit);
    }
}

void SqlQuery::execQuery(const QString &query, int columns)
{
    qDebug() << "exec::" << query;

    ui->treeWidget->clear();

    if (!sql.exec(query)) {
        ui->statusLabel->setText(tr("Error while executing query! %1").arg(sql.lastError().text()));
        return;
    }

    QList<QTreeWidgetItem *> items;
    int rowCount = 0;
    while (sql.next()) {
        QStringList row;
        for (int i = 0; i < columns; ++i)
            row << sql.value(i).toString();
        items.append(new QTreeWidgetItem((QTreeWidget*)0, row));
        rowCount++;
    }
    ui->treeWidget->insertTopLevelItems(0, items);

    for(int i = 0; i < columns; ++i)
        ui->treeWidget->resizeColumnToContents(i);

    m_query["row_count"] = rowCount;
    ui->statusLabel->setText(tr("Query executed, %1 rows").arg(rowCount));
}
